﻿using DBUtil.MetaData;
using DBUtil.Provider.MySql.MetaData;
using DotNetCommon.Data;
using DotNetCommon.Extensions;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

namespace DBUtil.Provider.MySql
{
    public partial class MySqlManage : DBManage
    {
        #region 生成insert语句

        public override (long count, string sql, long total) GenerateInsertSql(string tableName, int limitCount = 0, string filter = null, IDictionary<string, object> parameters = null)
        {
            var objName = db.ParseObjectName(tableName);
            var res = "";
            var table = ShowTableDetail(tableName);
            res += $"-- 开始执行insert语句\r\n";
            var insertSql = "";
            var colNames = table.Columns.Select(col => $"`{col.Name}`").ToList();
            insertSql = $"insert into {objName.NormalNameQuoted}({string.Join(",", colNames)}) ";
            var count = 0;
            db.SelectDataReader(reader =>
            {
                var index = 0;
                while (reader.RawReader.Read())
                {
                    string sql = string.Empty;
                    if (index == 0)
                    {
                        sql = insertSql + " values\r\n\t (";
                    }
                    else
                    {
                        sql = "\t,(";
                    }
                    index++;
                    var values = new List<string>();
                    foreach (var col in table.Columns)
                    {
                        var value = reader.RawReader[col.Name];
                        if (value == DBNull.Value)
                        {
                            values.Add("NULL");
                        }
                        else if (value is string)
                        {
                            values.Add("'" + db.EscapeString(value.ToString()) + "'");
                        }
                        else if (value is byte[])
                        {
                            var bs = (byte[])value;
                            var sb = new StringBuilder();
                            sb.Append("0x");
                            foreach (var by in bs)
                            {
                                sb.Append(by.ToString("X2"));
                            }
                            values.Add(sb.ToString());
                        }
                        else if (value is DateTime || value is DateTimeOffset || value is TimeSpan || value is MySqlConnector.MySqlDateTime)
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value is Guid)
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value is bool)
                        {
                            var t = (bool)value;
                            values.Add(t ? "1" : "0");
                        }
                        else
                        {
                            values.Add(db.EscapeString(value.ToString()));
                        }
                    }
                    sql += $"{string.Join(",", values)})";
                    res += $"\r\n{sql}";
                    count++;
                    if (limitCount > 0 && limitCount == count) break;
                };
            }, $"select * from {objName.NormalNameQuoted} {db.LinkWhereFilterSeg(filter)}", parameters);
            res += ";";

            res += $"\r\n-- {objName.NormalNameQuoted}: 共执行了{count}次insert(自行判断执行成功或失败)'";
            var total = GetCount(tableName);
            return (count, res, total);
        }

        public override long GenerateInsertSqlFile(string tableName, string fileAbsPath, int limitCount = 0, string filter = null, IDictionary<string, object> parameters = null)
        {
            var objName = db.ParseObjectName(tableName);
            var dir = Path.GetDirectoryName(fileAbsPath);
            if (!Directory.Exists(dir))
            {
                lock (typeof(MySqlManage))
                {
                    if (!Directory.Exists(dir))
                    {
                        Directory.CreateDirectory(dir);
                    }
                }
            }
            var table = ShowTableDetail(tableName);
            File.AppendAllText(fileAbsPath, $"-- 开始执行insert语句 {objName.NormalNameQuoted}:");
            var insertSql = "";
            var colNames = table.Columns.Select(col => $"`{col.Name}`").ToList();
            insertSql = $"insert into {objName.NormalNameQuoted}({string.Join(",", colNames)}) ";
            var count = 0L;
            var countLimit = 0;
            return db.SelectDataReader(reader =>
             {
                 var index = 0;
                 while (reader.RawReader.Read())
                 {
                     string sql = string.Empty;
                     if (index == 0)
                     {
                         sql = insertSql + " values\r\n\t (";
                     }
                     else
                     {
                         sql = "\t,(";
                     }
                     index++;
                     var values = new List<string>();
                     foreach (var col in table.Columns)
                     {
                         var value = reader.RawReader[col.Name];
                         if (value == DBNull.Value)
                         {
                             values.Add("NULL");
                         }
                         else if (value is string)
                         {
                             values.Add("'" + db.EscapeString(value.ToString()) + "'");
                         }
                         else if (value is byte[])
                         {
                             var bs = (byte[])value;
                             var sb = new StringBuilder();
                             sb.Append("0x");
                             foreach (var by in bs)
                             {
                                 sb.Append(by.ToString("X2"));
                             }
                             values.Add(sb.ToString());
                         }
                         else if (value is DateTime || value is DateTimeOffset || value is TimeSpan || value is MySqlConnector.MySqlDateTime)
                         {
                             values.Add("'" + value.ToString() + "'");
                         }
                         else if (value is Guid)
                         {
                             values.Add("'" + value.ToString() + "'");
                         }
                         else if (value is bool)
                         {
                             var t = (bool)value;
                             values.Add(t ? "1" : "0");
                         }
                         else
                         {
                             values.Add(db.EscapeString(value.ToString()));
                         }
                     }
                     sql += $"{string.Join(",", values)})";
                     File.AppendAllText(fileAbsPath, $"\r\n{sql}");
                     count++;
                     countLimit++;
                     if (limitCount > 0 && limitCount == countLimit) break;
                 };
                 File.AppendAllText(fileAbsPath, $";");
                 File.AppendAllText(fileAbsPath, $"\r\n-- {objName.NormalNameQuoted}: 共执行了{count}次insert(自行判断执行成功或失败)'");
                 return count;
             }, $"select * from {objName.NormalNameQuoted} {db.LinkWhereFilterSeg(filter)}", parameters);
        }

        public override string GenerateCreateTableSql(string tableName)
        {
            var sql = $"show create table {db.QuotedName(tableName)}";
            var dt = db.SelectDataTable(sql);
            if (dt.Rows.Count < 1) return "";
            var res = $"-- drop table if exists {db.QuotedName(tableName)};";
            res += "\r\n" + dt.Rows[0][1].ToString() + ";";
            return res;
        }

        public override void GenerateCreateTableSqlFile(List<Table> tables, string fileAbsPath, bool includeInsertSql = false)
        {
            var dir = Path.GetDirectoryName(fileAbsPath);
            if (!Directory.Exists(dir))
            {
                lock (typeof(MySqlManage))
                {
                    if (!Directory.Exists(dir))
                    {
                        Directory.CreateDirectory(dir);
                    }
                }
            }
            var details = tables;
            //生成脚本,不包含外键约束
            foreach (var detail in details)
            {
                File.AppendAllText(fileAbsPath, $"#-------------------------- 表:`{detail.Name}`" + "\r\n");
                var sql = GenerateCreateTableSql(detail.Name);
                File.AppendAllText(fileAbsPath, sql + "\r\n");
                File.AppendAllText(fileAbsPath, $"#------------------------------------------------------------------" + "\r\n");
            }
            File.AppendAllText(fileAbsPath, $"#-------------------------- 建表结束,共建表:{details.Count}个---------------------" + "\r\n\r\n");

            //插入数据
            if (includeInsertSql)
            {
                File.AppendAllText(fileAbsPath, $"\r\n#-------------------------- 开始插入数据" + "\r\n");
                details.ForEach(detail =>
                {
                    File.AppendAllText(fileAbsPath, $"#-------------------------- 插入到表:`{detail.Name}`" + "\r\n");
                    var (count, sqlInsert, total) = GenerateInsertSql(detail.FullNameQuoted);
                    File.AppendAllText(fileAbsPath, sqlInsert + "\r\n");
                    File.AppendAllText(fileAbsPath, $"#------------------------------------------------------------------------------\r\n");
                });
            }

            return;
        }

        public string GenerateCreateProcSql(string procName)
        {
            var sql = $"show create procedure {db.QuotedName(procName)}";
            var dt = db.SelectDataTable(sql);
            if (dt.Rows.Count < 1) return "";
            var res = $"-- drop procedure if exists {db.QuotedName(procName)};";
            res += "\r\n" + dt.Rows[0]["Create Procedure"].ToString() + ";";
            return res;
        }

        public override string GenerateCode(string tableName)
        {
            return _generateCode(tableName);
        }

        private string _generateCode(string tableName, bool isView = false)
        {
            var code = "";
            MySqlTable table = null;
            MySqlView view = null;
            if (isView)
            {
                view = ShowViewDetail(tableName) as MySqlView;
            }
            else
            {
                table = ShowTableDetail(tableName) as MySqlTable;
            }
            var name = (isView ? view.Name : table.Name);
            code += $@"
using System;
using System.Collections.Generic;
using System.Linq;
using Newtonsoft.Json;
using DotNetCommon.Serialize; // dotnet add package DotNetCommon
public class {formmaterName(name)}
{{
        public const string TABLE_NAME = ""{name}"";
        public const string KEY_NAME = ""{table?.PrimaryKey}"";
        private static List<string> allColumns;
        static {formmaterName(name)}()
        {{
            allColumns = new List<string>();
            var type = typeof({name});
            var props = type.GetProperties().ToList();
            props.ForEach(prop => allColumns.Add(prop.Name));
        }}
        public static List<string> AllColumns => allColumns;
";
            var columns = isView ? view.Columns : table.Columns;
            var extEnum = "";
            foreach (var item in columns)
            {
                var col = item as MySqlColumn;
                var type = "";
                switch (col.Type)
                {
                    case EnumMySqlColumnType.Bit:
                        if (col.NUMERIC_PRECISION == 1) type = "bool";
                        if (col.NUMERIC_PRECISION == 8) type = "sbyte";
                        if (col.NUMERIC_PRECISION == 16) type = "ushort";
                        if (col.NUMERIC_PRECISION == 32) type = "uint";
                        if (col.NUMERIC_PRECISION == 64) type = "ulong";
                        type = "ulong";
                        break;

                    case EnumMySqlColumnType.TinyInt: type = "byte"; break;
                    case EnumMySqlColumnType.TinyIntUnSigned: type = "sbyte"; break;
                    case EnumMySqlColumnType.SmallInt: type = "short"; break;
                    case EnumMySqlColumnType.SmallIntUnSigned: type = "ushort"; break;
                    case EnumMySqlColumnType.MediumInt: type = "int"; break;
                    case EnumMySqlColumnType.MediumIntUnSigned: type = "uint"; break;
                    case EnumMySqlColumnType.Int: type = "int"; break;
                    case EnumMySqlColumnType.IntUnSigned: type = "uint"; break;
                    case EnumMySqlColumnType.BigInt: type = "long"; break;
                    case EnumMySqlColumnType.BigIntUnSigned: type = "ulong"; break;

                    case EnumMySqlColumnType.Float: type = "double"; break;
                    case EnumMySqlColumnType.Double: type = "double"; break;
                    case EnumMySqlColumnType.Decimal: type = "decimal"; break;

                    case EnumMySqlColumnType.Char:
                    case EnumMySqlColumnType.VarChar:
                    case EnumMySqlColumnType.TinyText:
                    case EnumMySqlColumnType.Text:
                    case EnumMySqlColumnType.MediumText:
                    case EnumMySqlColumnType.LongText:
                        type = "string";
                        break;

                    case EnumMySqlColumnType.Binary:
                    case EnumMySqlColumnType.VarBinary:
                    case EnumMySqlColumnType.TinyBlob:
                    case EnumMySqlColumnType.Blob:
                    case EnumMySqlColumnType.MediumBlob:
                    case EnumMySqlColumnType.LongBlob:
                        type = "byte[]";
                        break;

                    case EnumMySqlColumnType.Year: type = "int"; break;
                    case EnumMySqlColumnType.Date: //.net6以上使用dateonly?
                    case EnumMySqlColumnType.Time: //.net6以上使用timeonly?
                    case EnumMySqlColumnType.DateTime:
                    case EnumMySqlColumnType.TimeStamp:
                        type = "DateTime";
                        break;

                    case EnumMySqlColumnType.Enum:
                        type = $"Enum{col.Name}";
                        extEnum += generateEnum(col.TypeString, col.Name);
                        break;
                    case EnumMySqlColumnType.Set:
                        type = $"Enum{col.Name}";
                        extEnum += generateEnum(col.TypeString, col.Name, true);
                        break;

                    case EnumMySqlColumnType.Geometry:
                    case EnumMySqlColumnType.Point:
                    case EnumMySqlColumnType.LineString:
                    case EnumMySqlColumnType.Polygon:
                    case EnumMySqlColumnType.GeometryCollection:
                    case EnumMySqlColumnType.MultiPoint:
                    case EnumMySqlColumnType.MultiLineString:
                    case EnumMySqlColumnType.MultiPolygon:
                        type = "byte[]";
                        break;

                    default:
                        break;
                }
                if (col.IsNullAble)
                {
                    if (new string[] { "object", "byte[]", "string" }.Contains(type)) { }
                    else
                    {
                        type += "?";
                    }
                }
                var desc = col.Desc;
                desc = desc.Replace("\r\n", " ");
                if (type == "long")
                {
                    code += @$"

        /// <summary>
        /// {desc}
        /// </summary>
        [JsonConverter(typeof(NumberConverter), NumberConverterShip.Int64)]
        public {type} {formmaterName(col.Name)} {{ set; get; }}";
                }
                else
                {
                    code += @$"

        /// <summary>
        /// {desc}
        /// </summary>
        public {type} {formmaterName(col.Name)} {{ get; set; }}";
                }

            }
            code += "\r\n}";
            code += extEnum;
            return code;
        }

        private string generateEnum(string typestring, string colname, bool flags = false)
        {
            if (typestring.IsNullOrEmptyOrWhiteSpace()) return "";
            var code = $@"
public enum Enum{colname.ToUpperFirstLetter()}
{{
";
            if (flags)
            {
                code = $@"
[Flags]
public enum Enum{colname.ToUpperFirstLetter()}
{{
";
            }
            typestring = typestring.Substring(flags ? 3 : 4);
            typestring = typestring.Trim('(', ')');
            var arr = typestring.SplitAndTrimTo<string>(",").Select(i => i.Trim('\'')).ToList();
            var tmp = "";
            for (var i = 0; i < arr.Count; i++)
            {
                tmp += $"\t{arr[i]} = {(flags ? Math.Pow(2, i) : (i + 1))},\r\n";
                if (i == arr.Count - 1) tmp = tmp.TrimEnd('\r', '\n', ',');
            }
            code += tmp;
            code += @"
}";
            return code;
        }

        private string formmaterName(string name)
        {
            if (name.IsNullOrEmptyOrWhiteSpace()) return name;
            var segs = name.SplitAndTrimTo<string>("_");
            if (segs.Count == 1) return segs[0].ToUpperFirstLetter();
            var res = "";
            for (var i = 0; i < segs.Count; i++)
            {
                res += segs[i].ToUpperFirstLetter();
            }
            return res;
        }
        #endregion
    }
    public static class StringExtensions
    {
        /// <summary>
        /// 首字母大写
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static string ToUpperFirstLetter(this string str)
        {
            if (str.IsNullOrEmptyOrWhiteSpace()) return str;
            if (str.Length == 1) return str.ToUpper();
            return (str.FirstOrDefault() + "").ToUpper() + str.Skip(1).Take(str.Length - 1).ToStringSeparated("");
        }
    }

}
